﻿using System;
using System.Collections;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;
using FreeBitExcel.entity;
using FreeBitExcel.service.@interface;
using log4net;
using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.XSSF.UserModel.Helpers;

namespace FreeBitExcel.service
{
    public class ExcelManager : IExcelManager
    {
        IWorkbook _workbook = null;
        public ILog Logger => this.GetLogger();

        public List<string> LoadWorkSheets(string name)
        {
            using (FileStream fs = File.OpenRead(name))
            {
                List<string> sheets = new List<string>();
                _workbook?.Close();
                // 2007版本  
                if (name.IndexOf(".xlsx") > 0)
                    _workbook = new XSSFWorkbook(fs);
                // 2003版本  
                else if (name.IndexOf(".xls") > 0)
                    _workbook = new HSSFWorkbook(fs);

                if (_workbook != null)
                {
                    for (int i = 0; i < _workbook.NumberOfSheets; i++)
                    {
                        sheets.Add(_workbook.GetSheetAt(i).SheetName);
                    }
                }
                sheets.Reverse();
                return sheets;
            }
        }

        public DataTable ReadValuse(string sheeName)
        {
            Task.Run(() =>
            {
                if (_workbook != null)
                {
                    var sheet = _workbook.GetSheet(sheeName); //读取第一个sheet，当然也可以循环读取每个sheet 
                    if (sheet != null)
                    {
                        int rowCount = sheet.LastRowNum; //总行数  
                        if (rowCount > 0)
                        {
                            var allRow = sheet.LastRowNum; //行数数  
                            var row = sheet.GetRow(0);
                            int cellCount = row.LastCellNum; //列数  

                            Logger.Info($"共有{allRow}行,{cellCount}列,一共有{allRow * cellCount}数据需要处理,请等待...");


                            Logger.Info("处理必要的索引信息..");

                            //查询 最多10行
                            //必定有索引信息
                            var testRow = 10;
                            var testRowIndex = -1;
                            var testcolumIndex = -1;
                            if (allRow < 10)
                            {
                                testRow = allRow;
                            }
                            for (int rowNum = 0; rowNum < testRow; rowNum++)
                            {
                                row = sheet.GetRow(rowNum);
                                for (int columIndex = row.FirstCellNum; columIndex < cellCount; ++columIndex)
                                {
                                    var value = GetValue(row.GetCell(columIndex));
                                    if (value != null && value.Contains("活動分類"))
                                    {
                                        testRowIndex = rowNum;
                                        testcolumIndex = columIndex;
                                        goto BAction;
                                    }
                                }
                            }

                            BAction:
                            if (testRowIndex == -1)
                            {
                                MessageBox.Show($"前{testRow}行没有 \"活動分類\" 的索引信息,情确认!!!!");
                                return;
                            }
                            //处理projectIndex
                            row = sheet.GetRow(testRowIndex);
                            var tempIndex = -1; //记录临时变量
                            for (int i = 0; i < cellCount; i++)
                            {
                                var cell = row.GetCell(i);
                                var value = GetValue(cell);

                                if (value != null && value.Contains("redmine番号")) //redmine番号唯一
                                {
                                    if (tempIndex == -1)
                                    {
                                        //找到一个,不处理,让tempIndex记录
                                    }
                                    else
                                    {
                                        Tuple<int, int> tuple = new Tuple<int, int>(tempIndex - 1, i - 2); //第一个开始
                                        Person.ProjectIndexs.Add(tuple);
                                    }
                                    tempIndex = i;
                                }
                            }
                            //处理最后一个
                            if (tempIndex != -1)
                            {
                                Tuple<int, int> tuple = new Tuple<int, int>(tempIndex - 1, cellCount - 2);
                                Person.ProjectIndexs.Add(tuple);
                            }
                            tempIndex = -1; //重置

                            //为 peroson 分类
                            List<List<IRow>> rows = new List<List<IRow>>();
                            List<IRow> subRowList = new List<IRow>();
                            for (var i = testRowIndex ; i < allRow; i++)
                            {
                                var cRow = sheet.GetRow(i);
                                var value = GetValue(cRow.GetCell(testcolumIndex + 1)); //读取redmine番号
                                if (value != null && value.Contains("redmine番号"))
                                {
                                   
                                    if (tempIndex == -1)
                                    {
                                        //找到一个,不处理,让tempIndex记录
                                    }
                                    else
                                    {
                                        var person = new Person
                                        {
                                            StartRowIndex = tempIndex + 1, //下移一行
                                            EndRowIndex = i - 4,
                                            Name = GetValue(sheet.GetRow(tempIndex + 1).GetCell(0))
                                        }; //第二个开始
                                        persons.Add(person);
                                    }
                                    tempIndex = i;
                                }
                            }
                            //处理第一个
                            if (tempIndex != -1)
                            {
                                var person = new Person
                                {
                                    StartRowIndex = tempIndex + 1,
                                    EndRowIndex = allRow - 1,
                                    Name = GetValue(sheet.GetRow(tempIndex + 1).GetCell(0))
                                };
                                persons.Add(person);
                            }
                            tempIndex = -1; //重置
                            Logger.Debug($"处理{persons.Count}个成员成功");
                            //并行处理 todo
                            /* Parallel.ForEach(rows, (ts, pls, lon) =>
                            {
                                Logger.Info($"任务{Task.CurrentId} 线程{Thread.CurrentThread.ManagedThreadId} : 开始处理当前第{ts.RowNum}行");
                                
                                for (int i = ts.FirstCellNum; i < cellCount; ++i)
                                {
                                    var cell = ts.GetCell(i);
                                    GetValue(cell);
                                }
                                Logger.Info($"任务{Task.CurrentId} 线程{Thread.CurrentThread.ManagedThreadId} : 第{ts.RowNum}行处理完毕");
                            });*/

                            //普通处理
                            for (int rowNum = 0; rowNum < allRow; rowNum++)
                            {
                                List<string> values = new List<string>();

                                Logger.Info($"开始读取读取第{rowNum}行 ");
                                row = sheet.GetRow(rowNum);
                                for (int i = row.FirstCellNum; i < cellCount; ++i)
                                {
                                    var cell = row.GetCell(i);
                                    var value = GetValue(cell);
                                    values.Add(value);
                                }

                                if (values.Count == 0)
                                {
                                    return;
                                }

                                //处理
                            }
                        }

                        Logger.Info("数据读取完毕");
                        Logger.Info("处理结果");
                        HandleResults();
                    }
                }
            });


            return null;
        }


        private string GetValue(ICell cell)
        {
            string value = null;
            if (cell != null)
            {
                switch (cell.CellType)
                {
                    case CellType.Numeric:
                        Logger.Debug(
                            $"{cell.Row.RowNum}:{cell.ColumnIndex} Numeric:{cell.NumericCellValue.ToString(CultureInfo.InvariantCulture)}");
                        value = cell.NumericCellValue.ToString(CultureInfo.InvariantCulture);
                        break;
                    case CellType.Unknown:
                        Logger.Debug($"{cell.Row.RowNum}:{cell.ColumnIndex} Unknown:{cell}");
                        value = cell.ToString();
                        break;
                    case CellType.String:
                        Logger.Debug($"{cell.Row.RowNum}:{cell.ColumnIndex} String:{cell.StringCellValue}");
                        value = cell.StringCellValue;
                        break;
                    case CellType.Formula:
                        Logger.Debug($"{cell.Row.RowNum}:{cell.ColumnIndex} Formula:{cell}");
                        value = cell.ToString();
                        break;
                    case CellType.Blank:
                        break;
                    case CellType.Boolean:
                        Logger.Debug($"{cell.Row.RowNum}:{cell.ColumnIndex} Boolean:{cell.BooleanCellValue}");
                        value = cell.BooleanCellValue.ToString();
                        break;
                    case CellType.Error:
                        Logger.Debug($"{cell.Row.RowNum}:{cell.ColumnIndex} Error:{cell.ErrorCellValue}");
                        value = cell.ErrorCellValue.ToString();
                        break;
                    default:
                        throw new ArgumentOutOfRangeException();
                }
            }
            return value;
        }

        private ConcurrentBag<string> weekNameBags = new ConcurrentBag<string>();
        private ConcurrentBag<Person> persons = new ConcurrentBag<Person>();

        private void ParseValue(ICell cell, string v)
        {
            if (v == null)
            {
                return;
            }
            if (v.Contains("日週") && !weekNameBags.Contains(v))
            {
                weekNameBags.Add(v);
            }
        }

        public void HandleResults()
        {
            Logger.Debug($"weekNameBags:{weekNameBags.Count}");
            HashSet<string> hashSet = new HashSet<string>(weekNameBags);
            List<WeekPP> weekPps = new List<WeekPP>();
            foreach (var name in hashSet)
            {
                WeekPP week = new WeekPP();
                week.WeekName = name;
                Logger.Debug($"week:{week}");
                weekPps.Add(week);
            }
            weekPps.Reverse(); //翻转
        }

        public void Dispose()
        {
            _workbook?.Close();
            _workbook = null;
        }
    }
}